package cn.mw.cmdb.util;

import cn.mw.cmdb.entity.MwRelationInstanceParam;
import cn.mw.cmdb.entity.PropertyTypeStruct;
import com.alibaba.fastjson.JSON;
import com.alibaba.nacos.common.utils.CollectionUtils;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.*;
import java.util.stream.Collectors;

import static cn.mw.cmdb.param.SystemDevTypeEnum.scBankSystemDev;
import static cn.mw.cmdb.service.impl.InstanceViewImpl.*;
import static cn.mw.cmdb.util.ValConvertUtil.strValueConvert;

/**
 * @author qzg
 * @date 2021/6/24
 */

//这里所需要的参数是  表格文件名  表格的sheet的名称   表格的头部字段名称  头部对应的字段 和数据集合，以及response
public class ExportExcel {
    private static final Logger logger = LoggerFactory.getLogger("ExportExcel");

    public static boolean exportExcel(String excelName, String title, List<String> lableName, List<String> lable, List<Map> dataset, String pattern, HttpServletResponse response) throws IOException {

        Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
//        String fileName = excelName + "-" + milliSecond;
        String fileName = excelName + milliSecond + ".xlsx";
//        String fileNames = new String(fileName.getBytes("utf-8"),"iso8859-1")+".xlsx";

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ServletOutputStream outputStream = response.getOutputStream();
        boolean flag = false;
        Workbook workbook = null;
        if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else {
            try {
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e) {
                logger.error("fail to exportExcel param{}, case by {}", "", e);
            }
        }

        Sheet sheet = workbook.createSheet(title);
        CellStyle style = workbook.createCellStyle();
        //表头样式
        XSSFCellStyle headStyle = (XSSFCellStyle) workbook.createCellStyle();
        XSSFFont headFont = (XSSFFont) workbook.createFont();
        headFont.setFontHeightInPoints((short) 11);
        headStyle.setFont(headFont);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setFillBackgroundColor((short) 45333);
        headStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
        headStyle.setWrapText(true); //自动换行
        headStyle.setFont(headFont);
        // 列名
        Row row = sheet.createRow(0);
        for (int i = 0; i < lableName.size(); i++) {
            Cell cell = row.createCell(i);
            sheet.setColumnWidth(i, 6000);
            cell.setCellValue(lableName.get(i));
            cell.setCellStyle(headStyle);
        }
        if (dataset != null) {
            Iterator<Map> it = dataset.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                Map map = it.next();
                Set<String> mapKey = (Set<String>) map.keySet();
                for (int i = 0; i < lable.size(); i++) {
                    Cell cell = row.createCell(i);
                    Object obj = map.get(lable.get(i));
                    if (obj != null && obj instanceof Date) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        cell.setCellValue(sdf.format(obj));
                    } else if (obj != null && obj instanceof LocalDateTime) {
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        cell.setCellValue(df.format((TemporalAccessor) obj));
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Long) {
                        cell.setCellValue((Long) obj);
                    } else if (obj instanceof List) {
                        String jsonString = JSON.toJSONString( (List) obj);
                        cell.setCellValue(jsonString);
                    } else {
                        cell.setCellValue(strValueConvert(obj));
                    }
                }
            }
        }
        FileOutputStream fileOutputStream;
        try {
//            File file = new File("D:/" + fileName);
//            fileOutputStream = new FileOutputStream(file);
//            workbook.write(fileOutputStream);
//            fileOutputStream.close();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
            flag = true;
        } catch (FileNotFoundException e) {
            flag = false;
            logger.error("ExportExcel:文件不存在, case by {}", e);

        } catch (IOException e) {
            flag = false;
            logger.error("ExportExcel:文件写入错误, case by {}", e);
        }
        return flag;
    }

    /**
     * @param excelName excel名称
     * @param title     导出excel标题
     * @param lableName 导出表头名称
     * @param lable     导出表头字段id
     * @param dataset   导出数据
     * @param pattern
     * @param response
     * @param systemType 客户环境
     * @return
     * @throws IOException
     */
    public static boolean exportExcelTest(String excelName, String title, List<Map> lableName, List<String> lable, List<Map> dataset, String pattern, HttpServletResponse response,String systemType) throws IOException {

        Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
        String fileName = excelName + milliSecond + ".xlsx";
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ServletOutputStream outputStream = response.getOutputStream();
        boolean flag = false;
        Workbook workbook = null;
        if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else {
            try {
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e) {
                logger.error("fail to exportExcel param{}, case by {}", "", e);
            }
        }





        Sheet sheet = workbook.createSheet(title);


//        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
//        int maxLength = 5;
//        // 创建文本长度约束
//        DataValidationConstraint lengthConstraint = validationHelper.createTextLengthConstraint(
//                DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
//                String.valueOf(maxLength),
//                null
//        );
//        // 定义数据验证区域
//        CellRangeAddressList addressList = new CellRangeAddressList(0,  65535, 0,0);
//        // 创建数据验证对象
//        DataValidation validation = validationHelper.createValidation(lengthConstraint, addressList);
//        // 设置错误提示
//        validation.setShowErrorBox(true);
//        validation.createErrorBox("错误", "不能超过 " + maxLength + " 个字符。");
//        // 将验证添加到工作表
//        sheet.addValidationData(validation);
//
//        setIntegerValidation(sheet,1,true);
//        setIntegerValidation(sheet,2,false);
//        setDateValidation(sheet,3);


        CellStyle style = workbook.createCellStyle();
        //表头样式
        XSSFCellStyle headStyle = (XSSFCellStyle) workbook.createCellStyle();
        XSSFFont headFont = (XSSFFont) workbook.createFont();
        headFont.setFontHeightInPoints((short) 11);
        headStyle.setFont(headFont);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setFillBackgroundColor((short) 45333);
        headStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
        headStyle.setWrapText(true); //自动换行
        headStyle.setFont(headFont);

        //设置单元格格式：文本
        CellStyle cellStyle2 = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        cellStyle2.setDataFormat(format.getFormat("@"));
        // 列名
        Row row;
        boolean isStructFlag = false;
        for (int i = 0; i < lableName.size(); i++) {
            Map map = lableName.get(i);
            String type = strValueConvert(map.get(typeKey));
            if (PropertyTypeStruct.ID.equals(type)) {
                isStructFlag = true;
            }
        }

        Map<String, String> propertyName2ColumnMap = new HashMap<>();
        Map<String, List<MwRelationInstanceParam>> linkDataMap = new HashMap<>();
        List headList = valueHandle(lableName, sheet, isStructFlag, workbook, title, systemType, lable, propertyName2ColumnMap, linkDataMap);

        if (!linkDataMap.isEmpty()) {
            for (Map.Entry<String, List<MwRelationInstanceParam>> item : linkDataMap.entrySet()) {
                String targetPropertyId = item.getValue().get(0).getLinkTargetPropertyId();
                String linkPropertyId = item.getKey();
                if (propertyName2ColumnMap.containsKey(linkPropertyId) && propertyName2ColumnMap.containsKey(targetPropertyId)) {
                    addValidationToSheet(workbook, sheet, item.getValue().stream().collect(Collectors.groupingBy(MwRelationInstanceParam::getLinkPropertyName)),
                            propertyName2ColumnMap.get(targetPropertyId), propertyName2ColumnMap.get(linkPropertyId), 1, 65535);
                }
            }
        }

        int rownum = 0;
        for (Object obj : headList) {
            // 列名
            row = sheet.createRow(rownum);
            rownum++;
            if (obj != null && obj instanceof List) {
                List<String> lableList = (List<String>) obj;
                for (int i = 0; i < lableList.size(); i++) {
                    Cell cell = row.createCell(i);
                    sheet.setColumnWidth(i, 6000);
                    cell.setCellValue(lableList.get(i));
                    cell.setCellStyle(headStyle);
                }
            }
        }
        //设置单元格格式为文本
        for (int colNum = 0; colNum < lable.size(); colNum++) {
            sheet.setDefaultColumnStyle(colNum, cellStyle2);
        }
        int index = isStructFlag ? 1 : 0;
        if (dataset != null) {
            Iterator<Map> it = dataset.iterator();
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                Map map = it.next();
                Set<String> mapKey = (Set<String>) map.keySet();
                for (int i = 0; i < lable.size(); i++) {
                    Cell cell = row.createCell(i);
                    Object obj = map.get(lable.get(i));
                    if (obj != null && obj instanceof Date) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        cell.setCellValue(sdf.format(obj));
                    } else if (obj != null && obj instanceof LocalDateTime) {
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        cell.setCellValue(df.format((TemporalAccessor) obj));
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Long) {
                        cell.setCellValue((Long) obj);
                    } else if (obj instanceof List) {
                        List val = (List) obj;
                    } else {
                        cell.setCellValue(strValueConvert(obj));
                    }
                }
            }
        }


        FileOutputStream fileOutputStream;
        try {
//            File file = new File("D:/" + fileName);
//            fileOutputStream = new FileOutputStream(file);
//            workbook.write(fileOutputStream);
//            fileOutputStream.close();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
            flag = true;
        } catch (FileNotFoundException e) {
            flag = false;
            logger.error("ExportExcel:文件不存在, case by {}", e);

        } catch (IOException e) {
            flag = false;
            logger.error("ExportExcel:文件写入错误, case by {}", e);
        }
        return flag;
    }


    private static List valueHandle(List<Map> valueList, Sheet sheet, boolean isStructFlag, Workbook workbook, String title, String systemType, List<String> lable, Map<String, String> propertyName2ColumnMap, Map<String, List<MwRelationInstanceParam>> linkDataMap) {
        List<List<String>> handerList = new ArrayList();
        List<String> strArr1 = new ArrayList<>();
        List<String> strArr2 = new ArrayList<>();
        //结构体占用的列数  ``
        int indexNum = 0;
        if (CollectionUtils.isNotEmpty(valueList)) {
            int x = 0;
            for (int i = 0; i < valueList.size(); i++) {
                Map map = valueList.get(i);
                String type = strValueConvert(map.get(typeKey));
                String name = strValueConvert(map.get(nameKey));
                //四川环境需要设置下拉框设置
                if (scBankSystemDev.name().equals(systemType)) {
                    //获取excel需要联动的下拉数据
                    if (map.get(linkDataKey) != null && lable.size() == valueList.size()) {
                        linkDataMap.put(lable.get(i), (List<MwRelationInstanceParam>) map.get(linkDataKey));
                    } else {
                        //设置excel下拉数据
                        if (map.get(selectDataKey) != null && map.get(selectDataKey) instanceof List) {
                            List<String> selectListInfos = (List<String>) map.get(selectDataKey);
                            Collections.sort(selectListInfos);
                            String[] array = selectListInfos.toArray(new String[selectListInfos.size()]);
                            // 下拉设置需要计算结构体占用的列数
                            setDropDownBox(workbook, title, array, i + indexNum);
                        }
                    }
                }
                if (lable.size() == valueList.size()) {
                    propertyName2ColumnMap.put(lable.get(i), getColumnLetter(i + indexNum));
                }
                if (!isStructFlag) {
                    strArr1.add(name);
                } else {
                    if (PropertyTypeStruct.ID.equals(type) && map.get(structDataKey) != null) {
                        List<Map> structData = (List<Map>) map.get(structDataKey);
                        strArr1.add(name);
                        for (Map ms : structData) {
                            String structName = strValueConvert(ms.get(nameKey));
                            strArr2.add(structName);
                        }
                        int size = structData.size();
                        for (int y = 1; y < size; y++) {
                            strArr1.add("");
                        }
                        logger.info("第0-0行：第" + (i + indexNum) + "列-" + (i + indexNum + size - 1) + "列");
                        int startIndex = (i + indexNum);
                        int endIndex = (i + indexNum + size - 1);
                        if (endIndex > startIndex) {
                            sheet.addMergedRegion(new CellRangeAddress(0, 0, (i + indexNum), (i + indexNum + size - 1)));
                        }
                        indexNum += size - 1;
                    } else {
                        strArr1.add(name);
                        strArr2.add("");
                        int len = indexNum > 0 ? indexNum : 0;
                        logger.info("第0-1行：第" + (i + len) + "列-" + (i + len) + "列");
                        sheet.addMergedRegion(new CellRangeAddress(0, 1, (i + len), (i + len)));
                    }
                }
            }
        }
        handerList.add(strArr1);
        handerList.add(strArr2);
        return handerList;
    }

    private static String getColumnLetter(int colIndex) {
        StringBuffer column = new StringBuffer();
        while (colIndex >= 0) {
            int remainder = colIndex % 26;
            column.insert(0, (char) (remainder + 'A'));
            colIndex = (colIndex / 26) - 1;
        }
        return column.toString();
    }

    private static int getColumnIndex(String columName) {
        int colIndex = 0;
        for (int i = 0; i < columName.length(); i++) {
            colIndex = colIndex * 26 + (columName.charAt(i) - 'A' + 1);
        }
        return colIndex - 1;
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<MwRelationInstanceParam>> options, String keyColumn, String valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<>();

        //将新建的sheet页隐藏掉
        workbook.setSheetHidden(workbook.getNumberOfSheets() - 1, true);

        int rowIndex = 0;
        for (Map.Entry<String, List<MwRelationInstanceParam>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            firstLevelItems.add(parent);
            List<MwRelationInstanceParam> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell = null;

            for (MwRelationInstanceParam child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child.getRelationName());
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, getColumnIndex(valueColumn), getColumnIndex(valueColumn));
            DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet("typelist"));
            DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint("INDIRECT($" + keyColumn + "2)");
            targetSheet.addValidationData(dvHelper.createValidation(formulaListConstraint, regions));
        }
    }

    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, List<String> options, String column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);

        int rowIndex = 0;
        for (String option : options) {
            int columnIndex = 0;
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(option.toString());
        }
        targetSheet.addValidationData(setTypeListDataValidation(workbook, hiddenSheetName + "!$A$1:$A$" + options.size(), 1, 65535, getColumnIndex(column), getColumnIndex(column)));
    }

    private static void createName(Workbook workbook, String nameName, String formula) {
        try {
            if (workbook.getName(nameName) == null) {
                Name name = workbook.createName();
                name.setNameName(nameName);
                name.setRefersToFormula(formula);
            }
        } catch (Exception e) {
            logger.error("createName error!", e);
        }
    }

    /**
     * 不可数字开头
     *
     * @param name
     * @return
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    /**
     * 设置下拉框数据
     *
     * @param typeName 要渲染的sheet名称
     * @param values   下拉框的值
     * @param col      下拉列的下标
     * @author Hower Wong
     * @date 2022年5月27日
     */
    public static void setDropDownBox(Workbook workbook, String typeName, String[] values, Integer col) {
        String hiddenSheetName = "hidden";
        //获取所有sheet页个数
        int sheetTotal = workbook.getNumberOfSheets();
        //处理下拉数据
        if (values != null && values.length != 0) {
            //新建一个sheet页
            XSSFSheet hiddenSheet = (XSSFSheet) workbook.getSheet(hiddenSheetName);
            if (hiddenSheet == null) {
                hiddenSheet = (XSSFSheet) workbook.createSheet(hiddenSheetName);
                sheetTotal++;
            }
            // 获取数据起始行
            int startRowNum = hiddenSheet.getLastRowNum() + 1;
            int endRowNum = startRowNum;
            //写入下拉数据到新的sheet页中
            for (int i = 0; i < values.length; i++) {
                hiddenSheet.createRow(endRowNum++).createCell(0).setCellValue(values[i]);
            }
            //将新建的sheet页隐藏掉
            workbook.setSheetHidden(sheetTotal - 1, true);
            //获取新sheet页内容
            String strFormula = hiddenSheetName + "!$A$" + ++startRowNum + ":$A$" + endRowNum;
            // 设置下拉
            XSSFSheet mainSheet = (XSSFSheet) workbook.getSheet(typeName);
            mainSheet.addValidationData(setTypeListDataValidation(workbook, strFormula, 1, 65535, col, col));
        }
    }

    /**
     * 返回类型 DataValidation
     *
     * @param strFormula formula
     * @param firstRow   起始行
     * @param endRow     终止行
     * @param firstCol   起始列
     * @param endCol     终止列
     * @return 返回类型 DataValidation
     */
    public static DataValidation setTypeListDataValidation(Workbook workbook, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet("typelist"));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        return dvHelper.createValidation(formulaListConstraint, regions);
    }

    // 设置整数验证（正数或负数）
    private static void setIntegerValidation(Sheet sheet, int columnIndex, boolean positive) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint;
        if(positive){
            constraint = validationHelper.createIntegerConstraint(
                    DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,
                    "0", null);
        }else{
            constraint = validationHelper.createIntegerConstraint(
                    DataValidationConstraint.OperatorType.LESS_OR_EQUAL,
                    "0", null);
        }

        CellRangeAddressList addressList = new CellRangeAddressList(0, 1000, columnIndex, columnIndex);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        validation.setShowErrorBox(true);

        String errorMessage = positive ? "请输入一个正整数" : "请输入一个负整数";
        validation.createErrorBox("输入错误", errorMessage);

        sheet.addValidationData(validation);
    }

    // 设置日期格式验证
    private static void setDateValidation(Sheet sheet, int columnIndex) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.BETWEEN,
                "1900-01-01", "2100-12-31", "yyyy-MM-dd");

        CellRangeAddressList addressList = new CellRangeAddressList(0, 1000, columnIndex, columnIndex);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        validation.setShowErrorBox(true);
        validation.createErrorBox("输入错误", "请输入正确的日期格式 (yyyy-MM-dd)");

        sheet.addValidationData(validation);

        // 设置单元格显示日期格式
        CellStyle dateCellStyle = sheet.getWorkbook().createCellStyle();
        CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            Cell cell = row.createCell(columnIndex);
            cell.setCellStyle(dateCellStyle);
        }
    }

}


